package code14JDBC.练习;

import code14JDBC.Emp;
import code14JDBC.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 黄衡熙
 * @version 1.0
 */
/*
* // 两张表
// 班级表:主键、班级号(唯一不为空)、所属专业
// 学生表:主键、学号、姓名、年龄、所属班级(外键)
// 1.保存班级以及该班中的所有学生信息
// 2.根据班级号查询指定班级以及该班级中所有学生信息
// 3.查询所有学生以及其所属班级信息*/
public class Test_双表练习添加 {
    public static void main(String[] args) {
       /* Class c=new Class("3","大数据");
        Integer id=addClass(c);
        c.setId(id);
        Student s=new Student(3,"admin",22,id);
        Student s1=new Student(4,"lucy",23,id);
        addStudent(s);
        addStudent(s1);*/

        Class c = selectById(1);
        System.out.println(c);
         List<Student> list = selectAll();
        for (Student s : list) {
            System.out.println(s);
        }
    }

    public static Integer addClass(Class c) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer id = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_class (class_no,major)")
                    .append(" values(?,?) ")
                    .toString();
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, c.getClass_no());
            ps.setString(2, c.getMajor());
            ps.executeUpdate();
            System.out.println("添加成功");
            rs = ps.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getInt(1);
            }
            return id;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
    }

    public static void addStudent(Student student) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" insert into t_student(no,name,age,class_id) ")
                    .append(" values(?,?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, student.getNo());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getAge());
            ps.setInt(4, student.getClass_id());
            ps.executeUpdate();
            System.out.println("添加成功");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /*
     * 根据班级号查询指定班级以及该班级中所有学生信息
     * */
    public static Class selectById(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Class c = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select c.id,c.class_no,c.major,s.id 's.id',s.no,s.name,s.age,s.class_id ")
                    .append(" from t_class c left join t_student s ")
                    .append(" on c.id=s.class_id ")
                    .append(" where c.id=? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                if (c == null) {
                    c = new Class();
                    c.setId(rs.getInt("id"));
                    c.setClass_no(rs.getString("class_no"));
                    c.setMajor(rs.getString("major"));
                }
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getInt("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                c.addStudent(student);
            }
            return c;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
    }

    //查询所有学生以及其所属班级信息
    public static List<Student> selectAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<Student>();
        try {
            conn = JDBCUtil .getConnection();
            String sql = new StringBuffer()
                    .append(" select s.id,s.no,s.name,s.age,s.class_id,c.id 'c.id',c.class_no 'c.class_no',c.major 'c.major' ")
                    .append(" from t_student s ")
                    .append(" left join t_class c  ")
                    .append(" on s.class_id=c.id ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getInt("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                Class c = new Class();
                c.setId(rs.getInt("c.id"));
                c.setClass_no(rs.getString("c.class_no"));
                c.setMajor(rs.getString("c.major"));
                student.setC(c);
                students.add(student);
            }
            return students;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(conn, ps, rs);
        }
    }
}
